package com.atsmart.app.sql;

import java.util.ArrayList;
import java.util.List;

import com.atsmart.app.model.ClientInfo;
import com.atsmart.app.model.ClientListInfo;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

public class ClientSqlHelper {

	private SQLiteDatabase db = null;

	/**
	 * 打开数据库
	 */
	public void open(SqlHelper dh) {
		// dh = new SqlHelper(null, "atsmart", null,SqlHelper.getDBVersion());
		db = dh.getWritableDatabase();
	}

	public synchronized void addClientInfo(ClientInfo at) {
		if (at == null) {
			return;
		}
		String strSql = "insert or replace into client_info (pol_id,imgUrl,name,tmpNum,distance,address,tel,lon,lat) values(?,?,?,?,?,?,?,?,?);";
		try {
			SQLiteStatement stm = db.compileStatement(strSql);
			stm.bindLong(1, at.getId());
			stm.bindString(2, at.getImgUrl());
			stm.bindString(3, at.getName());
			stm.bindLong(4, at.getTmpNum());
			stm.bindDouble(5, at.getDistance());
			stm.bindString(6, at.getAddress());
			stm.bindString(7, at.getTel());
			stm.bindDouble(8, at.getLon());
			stm.bindDouble(9, at.getLat());
			stm.execute();
			stm.clearBindings();
			stm.close();
		} catch (Exception e) {
			// will not happen
		}
	}

	public ClientListInfo getClientInfo(int begin, int pagesize,String keyword) {
		String sql ="";
		if(keyword==null||"".equals(keyword)){
			 sql = " select _id,pol_id,imgUrl,name,tmpNum,distance,address,tel,lon,lat from client_info limit "
						+ begin + "," + pagesize;
		}else{
			 sql = " select _id,pol_id,imgUrl,name,tmpNum,distance,address,tel,lon,lat from client_info where name like '%"+keyword+"%'  limit "
						+ begin + "," + pagesize;
		}
		Cursor cursor = db.rawQuery(sql, null);
		ClientInfo historie = null;
		List<ClientInfo> nums = null;
		ClientListInfo t = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				nums = new ArrayList<ClientInfo>();
				t = new ClientListInfo();
				for (int i = 0; i < cursor.getCount(); i++) {
					historie = new ClientInfo();
					historie.setId(cursor.getInt(1));
					historie.setImgUrl(cursor.getString(2));
					historie.setName(cursor.getString(3));
					historie.setTmpNum(cursor.getInt(4));
					historie.setDistance(cursor.getFloat(5));
					historie.setAddress(cursor.getString(6));
					historie.setTel(cursor.getString(7));
					historie.setLon(cursor.getFloat(8));
					historie.setLat(cursor.getFloat(9));
					nums.add(historie);
					cursor.moveToNext();
				}
				t.setClients(nums);
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return t;
	}

	public ClientListInfo getClientDisInfo(int begin, int pagesize,String keyword) {
		String sql ="";
		if(keyword==null||"".equals(keyword)){
			 sql = " select c._id,c.pol_id,c.imgUrl,c.name,c.tmpNum,cd.distance,c.address,c.tel,c.lon,c.lat from client_info c,client_dis_info cd where c.pol_id=cd.pol_id order by cd.distance limit "
						+ begin + "," + pagesize;
		}else{
			 sql = " select c._id,c.pol_id,c.imgUrl,c.name,c.tmpNum,cd.distance,c.address,c.tel,c.lon,c.lat from client_info c,client_dis_info cd where c.pol_id=cd.pol_id  and name like '%"+keyword+"%'  order by cd.distance limit "
						+ begin + "," + pagesize;
		}
		Cursor cursor = db.rawQuery(sql, null);
		ClientInfo historie = null;
		List<ClientInfo> nums = null;
		ClientListInfo t = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				nums = new ArrayList<ClientInfo>();
				t = new ClientListInfo();
				for (int i = 0; i < cursor.getCount(); i++) {
					historie = new ClientInfo();
					historie.setId(cursor.getInt(1));
					historie.setImgUrl(cursor.getString(2));
					historie.setName(cursor.getString(3));
					historie.setTmpNum(cursor.getInt(4));
					historie.setDistance(cursor.getFloat(5));
					historie.setAddress(cursor.getString(6));
					historie.setTel(cursor.getString(7));
					historie.setLon(cursor.getFloat(8));
					historie.setLat(cursor.getFloat(9));
					nums.add(historie);
					cursor.moveToNext();
				}
				t.setClients(nums);
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return t;
	}
	public int getCount() {
		String sql = " select _id from client_info ";
		Cursor cursor = db.rawQuery(sql, null);
		try {
			if (cursor != null && cursor.moveToFirst()) {
				return cursor.getCount();
			}
		} catch (Exception e) {
			return 0;
		} finally {
			cursor.close();
		}
		return 0;
	}

	public void deleteAttribute(int id) {
		// db.delete("position_attribute", null, null);
		db.execSQL("delete from client_info where pol_id="+id);
	}
	public void delete() {
		// db.delete("position_attribute", null, null);
		db.execSQL("delete from client_info ");
	}
}
